package com.jhx.mall.dao.impl;

import com.jhx.mall.dao.BaseDao;
import com.jhx.mall.dao.DtsGoodsDao;
import com.jhx.mall.entity.DtsAd;
import com.jhx.mall.entity.DtsCategory;
import com.jhx.mall.entity.DtsGoods;
import com.jhx.mall.entity.DtsGoodsAttribute;
import com.jhx.mall.vo.DtsCategoryVo;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DtsGoodsDaoImpl extends BaseDao implements DtsGoodsDao {

    @Override
    public List<DtsGoods> selectGoodsNew() {
        List<DtsGoods> goodsList = new ArrayList<>();
        String sql = "select id, goods_sn, name, pic_url, share_url, gallery, counter_price, " +
                "retail_price, approve_status, category_id, brand_id, keywords, brief, is_on_sale," +
                " sort_order, is_new, is_hot, unit, detail, add_time, update_time, browse, sales, deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price " +
                "from dts_goods where deleted = 0 and is_new = 1 limit ?,?";
        rs = super.executeQuery(sql, 0, 20);
        try {
            while (rs.next()) {
                DtsGoods goods = new DtsGoods();
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return goodsList;
    }

    @Override
    public List<DtsGoods> selectGoodsHot() {
        List<DtsGoods> goodsList = new ArrayList<>();
        String sql = "select id, goods_sn, name, pic_url, share_url, gallery, counter_price, " +
                "retail_price, approve_status, category_id, brand_id, keywords, brief, is_on_sale," +
                " sort_order, is_new, is_hot, unit, detail, add_time, update_time, browse, sales, deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price " +
                "from dts_goods where deleted = 0 and is_hot = 1 limit ?,?";
        rs = super.executeQuery(sql, 0, 20);
        try {
            while (rs.next()) {
                DtsGoods goods = new DtsGoods();
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return goodsList;
    }

    @Override
    public DtsGoods selectGoodsById(long id) {
        DtsGoods goods =  new DtsGoods();
        List<DtsGoodsAttribute> goodsAttributeList = new ArrayList<>();
        String sql = "select g.id as gId, goods_sn, name, pic_url, share_url, " +
                "gallery, counter_price, retail_price, approve_status, category_id, " +
                "brand_id, keywords, brief, is_on_sale,sort_order, is_new, is_hot, " +
                "unit, detail, g.add_time, g.update_time, browse, sales, g.deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price," +
                "attribute,value from dts_goods as g left join dts_goods_attribute as a on " +
                "g.id = a.goods_id where g.deleted = 0 and a.deleted = 0 and g.id = ?";
        rs = super.executeQuery(sql, id);
        try {
            while (rs.next()) {
                DtsGoodsAttribute attribute = new DtsGoodsAttribute();
                goods.setId(rs.getLong("gId"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                attribute.setAttribute(rs.getString("attribute"));
                attribute.setValue(rs.getString("value"));
                goodsAttributeList.add(attribute);
            }
            goods.setGoodsAttribute(goodsAttributeList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return goods;
    }

    @Override
    public List<DtsGoods> selectGoodsByCategoryId(long categoryId) {
        List<DtsGoods> goodsList = new ArrayList<>();
        String sql = "select id, goods_sn, name, pic_url, share_url, gallery, counter_price, " +
                "retail_price, approve_status, category_id, brand_id, keywords, brief, is_on_sale," +
                " sort_order, is_new, is_hot, unit, detail, add_time, update_time, browse, sales, deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price " +
                "from dts_goods where deleted = 0 and category_id = ? limit ?,?";
        rs = super.executeQuery(sql, categoryId, 0, 10);
        try {
            while (rs.next()) {
                DtsGoods goods = new DtsGoods();
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return goodsList;
    }

    @Override
    public List<DtsGoods> selectGoodsByDtsGoods(DtsGoods dtsGoods, long orderPrice) {
        conn = getConnection();
        List<DtsGoods> goodsList = new ArrayList<>();
        String sql = "select id, goods_sn, name, pic_url, share_url, gallery, counter_price, " +
                "retail_price, approve_status, category_id, brand_id, keywords, brief, is_on_sale," +
                " sort_order, is_new, is_hot, unit, detail, add_time, update_time, browse, sales, deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price " +
               "from dts_goods where deleted = 0";
        if (dtsGoods.getCategoryId() != 0) {
            sql += " and category_id = " + dtsGoods.getCategoryId();
        }
        if (dtsGoods.getKeywords() != null) {
            sql += " and keywords like '%" + dtsGoods.getKeywords() + "%'";
        }
        if (dtsGoods.getName() != null) {
            sql += " and `name` like '%" + dtsGoods.getName() + "%'";
        }
        if (orderPrice == 2) {
            sql += " order by counter_price desc";
        } else if (orderPrice == 1) {
            sql += " order by counter_price asc";
        }

        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                DtsGoods goods = new DtsGoods();
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
           e.printStackTrace();
        }
        return goodsList;
    }

    @Override
    public DtsCategoryVo selectGoodsByCategoryVo(DtsCategory dtsCategory) {
        DtsCategoryVo categoryVo = new DtsCategoryVo();
        List<DtsGoods> dtsGoodsList = new ArrayList<>();
        String sql = "SELECT id, goods_sn, name, pic_url, share_url, gallery, counter_price,retail_price, " +
                "approve_status, category_id, brand_id, keywords, brief, is_on_sale,sort_order, is_new, " +
                "is_hot, unit, detail, add_time, update_time, browse, sales, deleted,commpany, wholesale_price, " +
                "approve_msg, brokerage_type, brokerage_price FROM dts_goods as g WHERE g.deleted = 0 and " +
                "EXISTS (select id from dts_category as c where pid = ? and g.category_id = c.id and c.deleted = 0) LIMIT 0,20";
        rs = super.executeQuery(sql, dtsCategory.getId());
        try {
            while(rs.next()) {
                DtsGoods goods = new DtsGoods();
                if (dtsCategory.getId() != 0) {
                    categoryVo.setId(dtsCategory.getId());
                }
                if (dtsCategory.getName() != null) {
                    categoryVo.setName(dtsCategory.getName());
                }
                if (dtsCategory.getPid() != 0) {
                    categoryVo.setPid(dtsCategory.getPid());
                }
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                dtsGoodsList.add(goods);
            }
            categoryVo.setGoodsList(dtsGoodsList);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return categoryVo;
    }

    @Override
    public List<DtsGoods> selectGoodsByCateId(Long categoryId) {
        List<DtsGoods> goodsList = new ArrayList<>();
        String sql = "select id, goods_sn, name, pic_url, share_url, gallery, counter_price, " +
                "retail_price, approve_status, category_id, brand_id, keywords, brief, is_on_sale," +
                " sort_order, is_new, is_hot, unit, detail, add_time, update_time, browse, sales, deleted, " +
                "commpany, wholesale_price, approve_msg, brokerage_type, brokerage_price " +
                "from dts_goods where deleted = 0 and category_id=?";
        rs = super.executeQuery(sql, categoryId);
        try {
            while (rs.next()) {
                DtsGoods goods = new DtsGoods();
                goods.setId(rs.getLong("id"));
                goods.setName(rs.getString("name"));
                goods.setBrief(rs.getString("goods_sn"));
                goods.setPicUrl(rs.getString("pic_url"));
                goods.setShareUrl(rs.getString("share_url"));
                goods.setGallery(rs.getString("gallery"));
                goods.setCounterPrice(rs.getDouble("counter_price"));
                goods.setRetailPrice(rs.getDouble("retail_price"));
                goods.setApproveStatus(rs.getLong("approve_status"));
                goods.setCategoryId(rs.getLong("category_id"));
                goods.setBrandId(rs.getLong("brand_id"));
                goods.setKeywords(rs.getString("keywords"));
                goods.setBrief(rs.getString("brief"));
                goods.setIsOnSale(rs.getLong("is_on_sale"));
                goods.setSortOrder(rs.getLong("sort_order"));
                goods.setIsNew(rs.getLong("is_new"));
                goods.setIsHot(rs.getLong("is_hot"));
                goods.setUnit(rs.getString("unit"));
                goods.setDetail(rs.getString("detail"));
                goods.setAddTime(rs.getString("add_time"));
                goods.setUpdateTime(rs.getString("update_time"));
                goods.setBrowse(rs.getLong("browse"));
                goods.setSales(rs.getLong("sales"));
                goods.setDeleted(rs.getLong("deleted"));
                goods.setCommpany(rs.getString("commpany"));
                goods.setWholesalePrice(rs.getDouble("wholesale_price"));
                goods.setApproveMsg(rs.getString("approve_msg"));
                goods.setBrokerageType(rs.getLong("brokerage_type"));
                goods.setBrokeragePrice(rs.getDouble("brokerage_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return goodsList;
    }
}
